{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from IPython.core.display import display, HTML\n",
    "display(HTML(\"<style>.container { width:100% !important; }</style>\"))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Table of Contents\n",
    "* [Lecture 2A - Introduction to working with data using Python Pandas*](#Lecture-2A---Introduction-to-working-with-data-using-Python-Pandas*)\n",
    "\t* &nbsp;\n",
    "\t\t* [Content](#Content)\n",
    "\t\t* [Learning Outcomes](#Learning-Outcomes)\n",
    "\t* [Pandas Data Structures](#Pandas-Data-Structures)\n",
    "\t\t* [Series - Univariate Data](#Series---Univariate-Data)\n",
    "\t\t* [Selecting values based on index](#Selecting-values-based-on-index)\n",
    "\t\t* [Basic plotting](#Basic-plotting)\n",
    "\t\t* [Selecting data points based on values](#Selecting-data-points-based-on-values)\n",
    "\t\t* [Indices and alignment](#Indices-and-alignment)\n",
    "\t* [File input](#File-input)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Lecture 2A - Introduction to working with data using Python Pandas*\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Content"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "1. Why pandas...\n",
    "2. pandas' Series data structure\n",
    "3. Selecting and filtering data from Series\n",
    "4. Basic plotting with Series\n",
    "5. Series indices and alignment\n",
    "6. Open files and load file data into Series\n",
    "\n",
    "\\* This notebook material is adapted from Assoc. Prof. Fonnesbeck's tutorial on statistical data analysis in Python and closely follows \"Python for Data Analysis: Data Wrangling with Pandas, NumPy, and IPython\" By Wes McKinney."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Learning Outcomes"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "At the end of this lecture, you should be able to:\n",
    "\n",
    "* describe the reasons for using the Series data type  \n",
    "* select data from Series using the index \n",
    "* filter data points from Series using their values \n",
    "* perform plotting of Series data at an introductory level\n",
    "* explain the role of indices in Series and how operations on multiple Series objects use indices\n",
    "* load univariate data into a Series object from a flat file"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "\n",
    "**pandas** is a package that is a fundamental ingredient in enabling working with structured data in a fast, easy and expressive manner using Python. It provides powerful data structures with all the key manipulation operations enabling, slicing and dicing, aggregating, integrating and extracting data. Pandas is particularly designed and well suited for working with **two dimensional data** that is arranged as SQL tables or Excel spreadsheets, where the data can of **different types**.\n",
    "\n",
    "Pandas key features:\n",
    "    \n",
    "- Easy handling of **missing data**\n",
    "- **Size mutability**: columns can be inserted and deleted from DataFrame and higher dimensional objects\n",
    "- Automatic and explicit **data alignment**: objects can be explicitly aligned to a set of labels, or the data can be aligned automatically\n",
    "- Powerful, flexible **group by functionality** to perform split-apply-combine operations on data sets\n",
    "- Intelligent label-based **slicing, fancy indexing, and subsetting** of large data sets\n",
    "- Intuitive **merging and joining** data sets\n",
    "- Flexible **reshaping and pivoting** of data sets\n",
    "- **Hierarchical labeling** of axes\n",
    "- Robust **IO tools** for loading data from flat files, Excel files, databases, JSON, CSV, XML formats\n",
    "- **Time series functionality**: date range generation and frequency conversion, moving window statistics, moving window linear regressions, date shifting and lagging, etc."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from IPython.core.display import HTML\n",
    "HTML(\"<iframe src=http://pandas.pydata.org width=800 height=350></iframe>\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import matplotlib.pyplot as plt"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.__version__"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#import some extra plotting libraries that pretty-fy the plots \n",
    "import seaborn as sns\n",
    "from pylab import rcParams\n",
    "\n",
    "rcParams['figure.dpi'] = 350\n",
    "rcParams['lines.linewidth'] = 2\n",
    "rcParams['axes.facecolor'] = 'white'\n",
    "rcParams['patch.edgecolor'] = 'white'\n",
    "rcParams['font.family'] = 'StixGeneral'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#this line enables the plots to be embedded into the notebook\n",
    "%matplotlib inline"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Set some Pandas options for displaying the data as you like\n",
    "pd.set_option('max_columns', 40)\n",
    "pd.set_option('max_rows', 20)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Pandas Data Structures"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The real power of pandas lies in its data structures. The two structures we will look at are the Series and DataFrame, where the DataFrame is the key and the most powerful in the pandas suite."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Series - Univariate Data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "A **Series** is a single vector of data (like a list or array) with an *index* that labels each element in the vector. Series holds univariate data which represents multiple observations of some phenomenon, viewed from a single perspective (one variable)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#by calling the Series class and placing a list inside its parentheses, we create a series object\n",
    "my_series = pd.Series([3778000, 19138000, 20000, 447000])\n",
    "my_series"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If an index is not specified, a default sequence of integers is assigned as the index seen above.\n",
    "\n",
    "We can extract all the values in a series object. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "my_series.values"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Meanwhile, individual values can be accessed like in a list:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "my_series[1]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The entire index of a series object can also be extracted."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "my_series.index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "list(my_series.index)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can assign meaningful labels to the index, if they are available:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "country = pd.Series(\n",
    "                    [3778000, 19138000, 20000, 447000], \n",
    "                    index=['New Zealand', 'Australia', 'Cook Islands', 'Solomon Islands']\n",
    "                    )\n",
    "country"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In order to quickly plot and visualize the data in the Series data structure, we simply call plot which gives us a default line graph:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "country.plot()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Selecting values based on index"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The new index values we created can now be used to refer to the values in the series object."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "country['Australia']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In order to access multiple values, we pass a **list** of index values:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "country[ ['New Zealand', 'Australia'] ]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Exercise**: Display the values for 'Australia', 'Cook Islands' and the 'Solomon Islands' using the above method."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "However, we do not have to use full index name to access the values, instead we can still use positional index values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "country[ [0, 1] ]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can also use slicing and avoid explicitly typing in the names or indexes of values we need to extract."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "country[ country.index[0:2] ]\n",
    "# or\n",
    "# country.iloc[0:2]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Exercise**: Display the values for 'Australia', 'Cook Islands' and the 'Solomon Islands' using slicing."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Additionally, we pass a list of booleans in order to print certain values "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "country[ [False, False, True, True] ]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This becomes handy when the values we wish to extract, depend on some boolean operation that we need to perform."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "list_of_values = []\n",
    "for val in country.index:\n",
    "    if 'Islands' in val:\n",
    "        list_of_values.append(True)\n",
    "    else:\n",
    "        list_of_values.append(False)\n",
    "\n",
    "print(list_of_values)\n",
    "country[ list_of_values ]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "There are almost always more ways than one way of coding for a particular requirement. The above can be shortened using functional programming constructs like list comprehension to the following:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "country[['Islands' in name for name in country.index]]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Exercise**: Using the long form construct above, write code below to list the values for countries whose name is smaller than 12 characters. Hint: if using the list comprehension, use the 'if' keyword to define the condition."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Exercise**: Try to replicate the same logic using list comprehension:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can give both the array of values and the index meaningful labels themselves:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "country.name = 'POPULATION'\n",
    "country.index.name = 'NATION'\n",
    "country"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Python's NumPy package adds support for large, multi-dimensional arrays and matrices, along with a large library of high-level mathematical functions to operate on these arrays. NumPy's math functions and other operations can be applied to Series objects at an element-wise level without losing the data structure."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#import numpy as np\n",
    "np.sqrt(country)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Basic plotting"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can also plot the result of Numpy maths operations on the Series in an easy manner:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "np.log(country).plot()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can easily change to different types of graphs. The options for graphs are ‘line’, ‘bar’, ‘barh’, ‘kde’, ‘density’ and ‘scatter’"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "_ = country.plot(kind='bar',rot=0)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Exercise**: Plot the country data using horizontal bar graphs (barh)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Selecting data points based on values"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can do filtering/selection based on an arbitrary number and more complex conditions as follows:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "country[country <= 20000]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "country[(country > 10000) & (country < 500000)]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Exercise**: plot a bar graph for countries with less than 1,000,000 population"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Indices and alignment"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "A `Series` can be thought of as an ordered **key-value** data structure. In fact, we can create one from a `dict`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "country_dict = {'New Zealand': 3778000, \n",
    "                'Australia': 19138000, \n",
    "                'Cook Islands': 20000, \n",
    "                'Solomon Islands': 447000}\n",
    "type(country_dict)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.Series(country_dict)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Notice that the `Series` is created in key-sorted order.\n",
    "\n",
    "If we pass a custom index to `Series`, it will select the corresponding values from the dict, and treat indices without corresponding values as **missing**. Pandas uses `NaN` (Not a Number) type for representing missing values."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "country2 = pd.Series(country_dict, index=['Niue','New Zealand', 'Australia', 'Cook Islands'])\n",
    "country2"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can find out which keys are associated with null values:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "country2.isnull()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Exercise**: plot a bar graph on country2 for countries that do not have null/NaN for population"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "One of the great advantages of working with pandas data structures, comes in their ability to ensure alignment. Critically, the labels are used to **align data** when used in operations with other Series objects:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "country"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "country2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "country + country2"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This is in contrast  with conventional python NumPy arrays, where arrays of the same length will combine values **element-wise**; adding Series combined values with the **same label** in the resulting series. Notice also that the **missing values were propagated by addition**."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## File input"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Up until this point, we have explored trivial datasets. Real-world datasets are much larger and are often stored in flat files that have to be opened and loaded into a program for processing. Below is an example of a file containing response times (in milliseconds) for queries against a web server or database, from the book \"Data Analysis with Open Source Tools\" by Philipp K. Janert."
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "452.42\n",
    "318.58\n",
    "144.82\n",
    "129.13\n",
    "1216.45\n",
    "991.56\n",
    "1476.69\n",
    "662.73\n",
    "1302.85\n",
    "1278.55\n",
    "627.65\n",
    "1030.78\n",
    "215.23\n",
    "44.50\n",
    "133.410\n",
    "619.96\n",
    "970.97\n",
    "1062.71\n",
    "87.83\n",
    "1068.87\n",
    "157.42\n",
    "492.97\n",
    "381.01\n",
    "..."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The data can be opened and read using pandas pd.read_csv() function and then converted into a Series object as follows:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "server_data = pd.read_csv(\"../datasets/ch02_serverdata\", header=None)[0]\n",
    "server_data.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Exercise**: From the output above, what is the first thing you can tell about the dataset that has been loaded?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Exercise**: Call the *describe()* function on the server_data object in the cell below."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}
